#!/usr/bin/python
# -*- coding: utf-8 -*-  
"""
@Project : hello 
@file : SimpleMySQLClient.py
@Author : sheen
@time : 2025/5/22 9:53
@func : 常用工具-小批量操作mysql的工具类
"""
from typing import Optional, Union, Any, List, Dict
import logging
import pymysql
from pymysql.cursors import DictCursor

from com.cn.for_cdc.common.cdc_conmons import mysql_pro_connections
from com.cn.for_cdc.common.log_helper import configure_logging

# 配置日志记录
configure_logging()


class SimpleMySQLClient:
    def __init__(
            self,
            host: str,
            user: str,
            password: str,
            port: int = 3306,
            database: Optional[str] = None,
            charset: str = "utf8mb4"
    ):
        """
        简化版MySQL客户端，适用于小批量操作
        :param charset: 显式声明字符集参数
        """
        self.conn_config = {
            'host': host,
            'user': user,
            'password': password,
            'port': port,
            'database': database,
            'charset': charset,
            'cursorclass': DictCursor
        }

    def _with_connection(self):
        """上下文管理器自动处理连接"""
        conn = pymysql.connect(**self.conn_config)
        try:
            with conn.cursor() as cursor:
                yield cursor
            conn.commit()
        except pymysql.Error as e:
            conn.rollback()
            raise
        finally:
            conn.close()

    def query(
            self,
            sql: str,
            params: Optional[Union[tuple, list, dict]] = None
    ) -> List[Dict[str, Any]]:
        """
        增强版查询方法
        :param params: 支持多种参数类型
        """
        try:
            with self._with_connection() as cursor:
                cursor.execute(sql, params)
                return cursor.fetchall()
        except pymysql.Error as e:
            logging.error(f"Query failed: {e}\nSQL: {sql}\nParams: {params}")
            raise RuntimeError(f"Query error: {e}") from e

    def execute(
            self,
            sql: str,
            params: Optional[Union[tuple, list, dict]] = None
    ) -> int:
        """统一化的执行方法"""
        try:
            with self._with_connection() as cursor:
                return cursor.execute(sql, params)
        except pymysql.Error as e:
            logging.error(f"Execute failed: {e}\nSQL: {sql}\nParams: {params}")
            raise RuntimeError(f"Execute error: {e}") from e

    def verify_connection(self) -> bool:
        """更可靠的连接检查"""
        try:
            conn = pymysql.connect(**self.conn_config)
            conn.ping(reconnect=False)
            logging.info(f"Connection valid: {self.conn_config}")
            return True
        except pymysql.Error as e:
            logging.warning(f"Connection invalid: {self.conn_config}")
            return False
        finally:
            if 'conn' in locals():
                conn.close()


if __name__ == '__main__':
    # 从配置获取参数示例
    bu = 'drlv'
    db_configs = mysql_pro_connections[f'{bu}_pro_job']
    client = SimpleMySQLClient(**db_configs)

    client.verify_connection()

    # # 连接验证
    # if client.verify_connection():
    #     # 查询示例
    #     users = client.query("SELECT * FROM users WHERE age > %s", (20,))
    #     # 插入示例
    #     client.execute(
    #         "INSERT INTO logs (message) VALUES (%s)",
    #         ("System started",)
    #     )
